Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Text

Friend MustInherit Class DBBase
    Protected msConnectionString As String
    Protected mlCommandTimeout As Long = 60

    Public Const NODATA As Short = -999

    Public Sub New()
        msConnectionString = ConfigurationManager.AppSettings("MetaData") & ";Application Name=SQLAutoDoc"
        Dim sTimeout As String = ConfigurationManager.AppSettings("MDTimeout")
        If IsNumeric(sTimeout) Then
            mlCommandTimeout = Val(sTimeout)
        End If
    End Sub

    Protected Function ValueToDBValue(ByVal oValue As Object, ByVal iMaxSize As Integer) As String
        Dim sReturn As String

        If oValue Is Nothing Then
            sReturn = "NULL"

        ElseIf oValue Is System.DBNull.Value Then
            sReturn = "NULL"

        ElseIf TypeOf oValue Is String Then
            If oValue.Length() > 0 AndAlso oValue.length() < iMaxSize Then
                sReturn = "'" & oValue.Replace("'", "''") & "'"

            ElseIf oValue.length() > iMaxSize Then
                sReturn = "'" & oValue.substring(0, iMaxSize).Replace("'", "''") & "'"

            Else
                sReturn = "NULL"
            End If

        ElseIf oValue.ToString = NODATA.ToString Then
            sReturn = "NULL"

        Else
            sReturn = "'" & oValue.ToString & "'"
        End If

        Return sReturn
    End Function

    Protected Function ValueToDBValue(ByVal oValue As Object) As String
        Dim sReturn As String

        If oValue Is Nothing Then
            sReturn = "NULL"

        ElseIf oValue Is System.DBNull.Value Then
            sReturn = "NULL"

        ElseIf TypeOf oValue Is String Then
            If oValue.Length() > 0 Then
                sReturn = "'" & oValue.Replace("'", "''") & "'"
            Else
                sReturn = "NULL"
            End If

        ElseIf oValue.ToString = NODATA.ToString Then
            sReturn = "NULL"

        Else
            sReturn = "'" & oValue.ToString & "'"
        End If

        Return sReturn
    End Function

    Protected Function NullToString(ByVal oValue As Object) As Object
        Dim oReturn As Object

        If oValue Is System.DBNull.Value Then
            oReturn = ""
        Else
            oreturn = oValue
        End If

        Return oreturn
    End Function

    Protected Function NullToNumber(ByVal oValue As Object) As Long
        Dim oReturn As Object

        If oValue Is System.DBNull.Value Then
            oReturn = NODATA
        Else
            oReturn = oValue
        End If

        Return oReturn
    End Function

    Protected Function BooleanToBit(ByVal sBoolean As String) As Byte
        Dim iReturnValue As Byte = 0

        If sBoolean.Substring(0, 1).ToUpper = "T" Then
            iReturnValue = 1
        End If

        Return iReturnValue
    End Function

    Public Function ExecuteNonQuery( _
                        ByVal sSQL As String, _
                        Optional ByVal sConnectionString As String = "") As Integer

        Dim iCount As Integer = -1

        Dim sCS As String = msConnectionString
        If sConnectionString > "" Then
            sCS = sConnectionString
        End If

        Dim oCon As New SqlConnection(sCS)
        oCon.Open()

        Dim oCMD As New SqlCommand(sSQL, oCon)
        oCMD.CommandTimeout = mlCommandTimeout

        iCount = oCMD.ExecuteNonQuery

        oCMD.Dispose()
        oCon.Close()
        oCon.Dispose()

        Return iCount
    End Function

    Public Function ExecuteScalar( _
                        ByVal sSQL As String, _
                        Optional ByVal sConnectionString As String = "") As Object

        Dim oObject As Object = Nothing

        Dim sCS As String = msConnectionString
        If sConnectionString > "" Then
            sCS = sConnectionString
        End If

        Dim oCon As New SqlConnection(sCS)
        oCon.Open()

        Dim oCMD As New SqlCommand(sSQL, oCon)
        oCMD.CommandTimeout = mlCommandTimeout

        oObject = oCMD.ExecuteScalar
        If oObject Is System.DBNull.Value Then oObject = Nothing

        oCMD.Dispose()
        oCon.Close()
        oCon.Dispose()

        Return oObject
    End Function

    Public Function ExecuteReadToDataTable( _
                            ByVal sSQL As String, _
                            Optional ByVal sConnectionString As String = "") As DataTable

        Dim sCS As String = msConnectionString
        If sConnectionString > "" Then
            sCS = sConnectionString
        End If

        Dim oCon As New SqlConnection(sCS)
        oCon.Open()

        Dim oCMD As New SqlCommand(sSQL, oCon)
        oCMD.CommandTimeout = mlCommandTimeout

        Dim oDT As New SqlDataAdapter(oCMD)
        Dim oData As New DataTable
        oDT.Fill(oData)

        oDT.Dispose()
        oCMD.Dispose()
        oCon.Close()
        oCon.Dispose()

        Return oData
    End Function

    Protected Function ToASCII_CSV(ByVal oValue As Object) As String
        Dim sResult As New StringBuilder("")

        If Not oValue Is Nothing _
            AndAlso Not oValue Is System.DBNull.Value Then

            Dim sValue As String = CStr(oValue)

            If sValue.Length > 0 Then
                sResult.Append(Asc(sValue.Substring(0, 1)))
                For i As Integer = 1 To sValue.Length - 1
                    sResult.Append("+" & Asc(sValue.Substring(i, 1)))
                Next
            End If
        End If

        If sResult.Length > 0 Then
            Return "'" & sResult.ToString & "'"
        Else
            Return "null"
        End If
    End Function

    Protected Function FromASCII_CSV(ByVal oValue As Object) As String
        Dim sResult As New StringBuilder("")

        If Not oValue Is Nothing _
            AndAlso Not oValue Is System.DBNull.Value Then

            Dim sValue As String = CStr(oValue)

            If sValue.Length > 0 Then
                Dim sList() As String = sValue.Split("+")
                For i As Integer = 0 To sList.GetUpperBound(0)
                    If IsNumeric(sList(i)) Then
                        sResult.Append(Chr(sList(i)))
                    Else
                        sResult = New StringBuilder("")
                        Exit For
                    End If
                Next
            End If
        End If

        Return sResult.ToString
    End Function
End Class
